#!/usr/bin/env python
#-*- coding:utf-8 -*-

import sys
import sqlite3

def getEstablishment():
	
	dbConn = sqlite3.connect("dbByPython.db")
	cursor = dbConn.cursor()
	
	establishmentNames = []
	
	for row in cursor.execute("select distinct(Nome_do_Estabelecimento) from rebides"):
		establishmentNames.append(row[0])
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return establishmentNames

def getYears():
	
	dbConn = sqlite3.connect("dbByPython.db")
	cursor = dbConn.cursor()
	
	years = []
	
	for row in cursor.execute("select distinct(Ano_Lectivo) from rebides"):
		years.append(row[0])
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return years

def getCategories():
	
	dbConn = sqlite3.connect("dbByPython.db")
	cursor = dbConn.cursor()
	
	categories = []
	
	for row in cursor.execute("select distinct(Categoria) from rebides"):
		categories.append(row[0])
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return categories
	
def getDegrees():
	
	dbConn = sqlite3.connect("dbByPython.db")
	cursor = dbConn.cursor()
	
	degrees = []
	
	for row in cursor.execute("select distinct(ultimo_Grau) from rebides"):
		degrees.append(row[0])
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return degrees
	
def getQuery(combo_box_1, combo_box_2, combo_box_3, combo_box_4):
	
	
	query = ""
	if(combo_box_1.Enabled==True):
		query = "select distinct(nome) from rebides where Nome_do_Estabelecimento = '" + combo_box_1.GetValue() + "'"
		if(combo_box_2.Enabled==True):
			query += " and Categoria = '" + combo_box_2.GetValue() + "'"
		if(combo_box_3.Enabled==True):
			query += " and ultimo_Grau = '" + combo_box_3.GetValue() + "'"
		if(combo_box_4.Enabled==True): 
			query += " and Ano_Lectivo = '" + str(combo_box_4.GetValue()) + "'"
	elif(combo_box_2.Enabled==True):
		query = "select distinct(nome) from rebides where Categoria = '" + combo_box_2.GetValue() + "'"
		if(combo_box_3.Enabled==True):
			query += " and ultimo_Grau = '" + combo_box_3.GetValue() + "'"
		if(combo_box_4.Enabled==True): 
			query += " and Ano_Lectivo = '" + str(combo_box_4.GetValue()) + "'"
	elif(combo_box_3.Enabled==True):
		query = "select distinct(nome) from rebides where ultimo_Grau = '" + combo_box_3.GetValue() + "'"
		if(combo_box_4.Enabled==True): 
			query += " and Ano_Lectivo = '" + str(combo_box_4.GetValue()) + "'"
	elif(combo_box_4.Enabled==True):
		query = "select distinct(nome) from rebides where Ano_Lectivo = '" + combo_box_4.GetValue() + "'"
	
	dbConn = sqlite3.connect("dbByPython.db")
    
	cursor = dbConn.cursor()
	
	result = []
	
	for row in cursor.execute(query):
		result.append(row[0])	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return result
